using System;
using System.Data;
using System.Collections.Generic;
namespace eTMS.DAO
{
    /********************************BEGIN KhachHangDotThiDAO****************************************/
    ///<summary>
    /// <para>Author: Auto generated class by CSharp Code Generator v2.0</para>
    /// <para>Date:27/09/2011 53:05:32</para>
    /// <para>Class Name: KhachHangDotThiDAO</para>
    /// <para>Description: Class access to table KhachHangDotThi in database. Class for coder code here</para>
    ///</summary>


    public partial class KhachHangDotThiDAO
    {
        public DataSet SelectAllDSWithKhachHangByDotThi(int DotThi_ID, string SBD, string TenKH)
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @"SELECT        KhachHang.KhachHang_ID, KhachHang.MaKhachHang, KhachHang.TenKhachHang, KhachHang.NgaySinh, KhachHang.GioiTinh,KhachHang.Email,KhachHang.CoDinh,KhachHang.DiDong,KhachHang.DiaChi, NganhHoc.TenNganhHoc, 
                                                         KhachHangDotThi.SBD, KhachHangDotThi.KhachHangDotThi_ID,  KhachHangDotThi.DotThi_ID
                                FROM            KhachHang INNER JOIN
                                                         KhachHangDotThi ON KhachHang.KhachHang_ID = KhachHangDotThi.KhachHang_ID
                                                        LEFT JOIN  NganhHoc ON KhachHang.NganhHoc_ID = NganhHoc.NganhHoc_ID
                                WHERE KhachHangDotThi.DotThi_ID=" + DotThi_ID.ToString();
            if (SBD != null && SBD.Trim().Length > 0)
            {
                cmd.CommandText += " AND KhachHangDotThi.SBD=@SBD";
                this.CreateCommandParameter(cmd, "@SBD", SBD);
            }
            if (TenKH != null && TenKH.Trim().Length > 0)
            {
                cmd.CommandText += " AND (KhachHang.TenKhachHang LIKE '%'+ @TenKH+ '%')";
                this.CreateCommandParameter(cmd, "@TenKH", TenKH);
            }
            return this.SelectDS(cmd);
        }
        public DataSet SelectAllDSKhachHangToAdd(int DotThi_ID)
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @"SELECT KhachHang.KhachHang_ID, KhachHang.MaKhachHang, KhachHang.TenKhachHang, KhachHang.NgaySinh, KhachHang.GioiTinh,KhachHang.Email,KhachHang.CoDinh,KhachHang.DiDong,KhachHang.DiaChi, NganhHoc.TenNganhHoc
                                FROM   KhachHang LEFT JOIN  NganhHoc ON KhachHang.NganhHoc_ID = NganhHoc.NganhHoc_ID
                                WHERE KhachHang.KhachHang_ID NOT IN(SELECT KhachHang_ID FROM KhachHangDotThi WHERE KhachHangDotThi.DotThi_ID=" + DotThi_ID.ToString() + ") AND KhachHang.KetThuc=0";
            return this.SelectDS(cmd);
        }
        public DataSet SelectAllDSByListSBD(List<string> lsSBD, int DotThi_ID)
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @"SELECT  KhachHangDotThi.SBD, KhachHangDotThi.KhachHangDotThi_ID 
                                FROM KhachHangDotThi
                                WHERE KhachHangDotThi.DotThi_ID=" + DotThi_ID.ToString();
            cmd.CommandText += " AND KhachHangDotThi.SBD IN (" + string.Join(",", lsSBD.ToArray()) + ")";
            return this.SelectDS(cmd);
        }
    }
    /*****************************************************************************************************/
    /**********************************END KhachHangDotThiDAO*****************************************/
    /*****************************************************************************************************/
}

